Data Warehouse Reference

OLTP vs OLAP, warehouse layers, schema design, slowly changing dimensions, ETL/ELT, and Redshift SQL.

1. Why a Warehouse

Operational systems (OLTP — Online Transactional Processing) and analytical systems (OLAP — Online Analytical Processing) have opposite needs. OLTP takes orders, fills stock, and handles complaints: one record at a time, fast writes, little history. OLAP answers "what's our best category?" or "how do sales compare to last month?": thousands of records scanned at once, fast read performance, deep historical context. A data warehouse is a database built and optimized specifically for that second job.

Warehouse goals: a centralized, consistent location for data pulled from many source systems; user-friendly enough for business users, not just engineers; loaded consistently and repeatedly via ETL; fast enough to serve reporting and visualization directly.

2. Warehouse Layers

• Staging area — a short-lived landing zone that extracts data quickly from source systems (sales data, CRM, etc.) and gets it off those systems fast, before any transformation happens.
• Core / access layer — the cleaned, conformed warehouse itself, after cleansing and integration transformations run against staged data.
• Data marts — department-scoped subsets of the core warehouse (e.g. one mart for Sales, one for HR), shaped around what each team actually queries.
• Predictive analytics — a further modeling layer built on top of the core warehouse or its marts.

Data lakes and warehouses both serve as centralized storage, but for different users and states of data: a lake holds raw, unstructured data for data scientists doing open-ended discovery; a warehouse holds processed, structured data for business users and IT running defined reports. Most organizations run both, side by side — see the AWS reference doc for the lake side of this comparison.

3. Star & Snowflake Schemas

Warehouse tables are modelled as fact tables (measurable events: an order, a click, a transaction; each row is one unit of activity at a declared grain) that reference dimension tables (the who/what/when/where context: customer, product, date, store). Star and snowflake schemas differ only in how the dimensions are structured:

Star schema

The fact table sits in the middle and every dimension joins to it directly, so the diagram looks like a star. A dimension's whole hierarchy lives in one table: if 1,000 products belong to "Electronics", the word "Electronics" is stored 1,000 times. That redundancy is deliberate.

                 dim_date
                    │
   dim_store ── fact_sales ── dim_product
                    │          (product_name, subcategory, category,
              dim_customer      department — all in one table)

-- one join from the fact to the category
SELECT p.category, SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_product p ON p.product_key = f.product_key
GROUP BY p.category;

Snowflake schema

Dimensions are normalized into separate tables for each level of their hierarchy, so the diagram branches out like a snowflake. Each category name is stored once, but reaching it takes more joins.

   fact_sales ── dim_product ── dim_subcategory ── dim_category
        │
     dim_store ── dim_city ── dim_region

-- three joins from the fact to the category
SELECT c.category_name, SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_product     p ON p.product_key    = f.product_key
JOIN dim_subcategory s ON s.subcategory_id = p.subcategory_id
JOIN dim_category    c ON c.category_id    = s.category_id
GROUP BY c.category_name;

Star vs snowflake

Common follow-up: "Is the fact table normalized?" In both schemas the fact table is normalized: it holds only foreign keys and measures. The schemas differ only in the dimensions, which is why a star schema is often described as "normalized facts, denormalized dimensions".

Which to choose?

• Star schema for most warehouses: columnar storage compresses repeated values so well that the extra storage costs little, while fewer joins mean faster queries and simpler SQL.
• Snowflake when a large hierarchy (such as geography) is shared by several dimensions, when a sub-dimension changes often and must be maintained in one place, or when tools already depend on the normalized structure.
• A common compromise: keep normalized tables in the integration/silver layer and publish flattened star dimensions in gold. Exercise 2 in "Star & Snowflake Design Exercises" converts a snowflake into a star.

4. Slowly Changing Dimensions

Dimension attributes change over time (a customer moves city, a product is re-categorized) and each SCD type handles that differently:

-- SCD Type 2 pattern
UPDATE dim_customer
SET expiry_date = CURRENT_DATE, is_current = false
WHERE customer_id = 101 AND is_current = true;

INSERT INTO dim_customer (customer_id, city, effective_date, expiry_date, is_current)
VALUES (101, 'Austin', CURRENT_DATE, NULL, true);

5. ETL vs ELT

• ETL (Extract, Transform, Load) — transform data in a separate processing layer before loading into the warehouse; the classic pattern when the warehouse's own compute is limited or expensive.
• ELT (Extract, Load, Transform) — load raw data straight into the warehouse and transform with its own SQL engine; the norm on cloud warehouses (Redshift, Snowflake, BigQuery) that have cheap, elastic compute.

6. Redshift SQL

Amazon Redshift is a managed, columnar, MPP (massively parallel processing) data warehouse — the AWS home for the core/access layer described above.

-- Load from S3 (COPY is Redshift's bulk-load command)
COPY sales.orders
FROM 's3://my-bucket/staging/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET;

-- Distribution & sort keys control how rows spread across nodes
CREATE TABLE fact_sales (
    order_id BIGINT,
    customer_id INT,
    date_id INT,
    amount DECIMAL(10,2)
)
DISTKEY(customer_id)
SORTKEY(date_id);

-- Redshift Spectrum: query data lake files directly, no load needed
SELECT region, SUM(amount)
FROM spectrum_schema.external_orders
WHERE order_year = 2026
GROUP BY region;

DISTKEY picks which column co-locates rows on the same node (usually the most common join key, to avoid shuffling during joins); SORTKEY orders each node's blocks so range filters (like a date range) skip blocks entirely.

7. Fact Table Types

Picking the grain first — "what does one row of this fact table mean?" — is the single most important dimensional-modeling decision; every measure in the table must be true at that grain.

8. Factless Fact Tables

A factless fact table is a fact table with no numeric measures: just foreign keys to dimensions (sometimes with a date or a degenerate ID). Each row records that an event happened or that a condition existed. You analyse it by counting rows rather than summing amounts.

Event tracking: attendance

CREATE TABLE fact_attendance (
  date_key    INT,          -- FK to dim_date
  student_key INT,          -- FK to dim_student
  course_key  INT           -- FK to dim_course
  -- no measures: the row itself is the event
);

-- How many attendances per course in October 2026?
SELECT c.course_name, COUNT(*) AS attendances
FROM fact_attendance f
JOIN dim_course c ON c.course_key = f.course_key
JOIN dim_date   d ON d.date_key   = f.date_key
WHERE d.year = 2026 AND d.month = 10
GROUP BY c.course_name
ORDER BY attendances DESC;

Coverage: finding what did not happen

A normal sales fact only has rows for things that sold, so it cannot answer "which promoted products did not sell?". A coverage table stores every product that was on promotion; an anti-join against the sales fact gives the gaps:

-- fact_promo_coverage: one row per product × store × day on promotion
SELECT pc.date_key, pc.store_key, pc.product_key
FROM fact_promo_coverage pc
LEFT JOIN fact_sales f
       ON f.date_key    = pc.date_key
      AND f.product_key = pc.product_key
      AND f.store_key   = pc.store_key
      AND f.promo_key   = pc.promo_key
WHERE f.product_key IS NULL;          -- promoted, but no promoted sale

Banking examples

-- cross-sell: eligible this month but not holding the product
SELECT e.customer_key, e.product_key
FROM fact_product_eligibility e
LEFT JOIN fact_product_holding h
       ON h.customer_key = e.customer_key
      AND h.product_key  = e.product_key
      AND h.month_key    = e.month_key
WHERE e.month_key = 202610
  AND h.customer_key IS NULL;

Interview points

• "Why not add a column with value 1?" You can (some BI tools need a measure to sum), but it is still factless in nature: COUNT(*) does the same job.
• Grain first: state exactly what one row means, e.g. "one row per student per class session attended".
• Coverage + transaction fact together answer "what was possible vs what happened": the classic factless use case.
• Keep the keys conformed (same dim_date, dim_product…) so factless and transaction facts can be compared directly.

9. Conformed Dimensions

A conformed dimension (e.g. dim_date, dim_customer) is built once and shared across every fact table and data mart that needs it, with identical column names, keys and meaning everywhere. Without conformance, "customer" in the Sales mart and "customer" in the Support mart can silently mean different things, making cross-mart reporting unreliable.

A bus matrix — rows of business processes (facts) against columns of shared dimensions — is the standard planning tool for keeping dimensions conformed as a warehouse grows across teams.

10. Query Performance

• Materialized views — precompute and store an expensive aggregate query's result, refreshed on a schedule or incrementally; trades storage and staleness for query speed.
• Columnar storage — Redshift stores each column separately, so a query touching 3 of 50 columns reads only those 3 — the same principle behind Parquet in a data lake.
• Result caching — Redshift automatically caches identical query results, serving repeats near-instantly without rescanning data.
• Workload management (WLM) — queues that separate fast dashboard queries from long-running batch ETL, so one doesn't starve the other of cluster resources.

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_id, region, SUM(amount) AS total_sales
FROM fact_sales
GROUP BY date_id, region;

REFRESH MATERIALIZED VIEW mv_daily_sales;

11. Normal Forms in Practice

3NF is the usual target for transactional models. 1NF and 2NF are steps on the way (1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF), and 2NF only matters when the key is composite.

Which form, where

# 1NF in Spark: explode an array column into one row per value
phones_1nf = raw_customers.select("customer_id",
                                  F.explode("phones").alias("phone_number"))

12. Normalization Step by Step

Normalization is the process of organising tables so that each fact is stored once, in the table whose key it truly depends on. It is driven by functional dependencies: A → B means "if you know A, you know exactly one B" (e.g. customer_id → customer_city). Normalized designs avoid three kinds of anomaly:

Worked example: from a spreadsheet to BCNF

0NF (unnormalized). One row per order, with a repeating group of products in a single cell:

1NF: atomic values, no repeating groups. One row per order line; the key becomes (order_id, product_id):

Queryable now, but all three anomalies remain: Asha's city is stored three times.

2NF: no partial dependencies on part of a composite key. order_date and the customer columns depend only on order_id; product_name depends only on product_id. Split them out:

orders      (order_id PK, order_date, customer_id, customer_name, customer_city)
products    (product_id PK, product_name, list_price)
order_lines (order_id, product_id, qty, unit_price,  PK (order_id, product_id))

Notice unit_price stays on order_lines: the price actually charged depends on the order line (Ben paid 450 for a mouse listed at 500). Only the current list price belongs to products. Telling these apart is a favourite interview follow-up.

3NF: no transitive dependencies (non-key column → non-key column). In orders, order_id → customer_id → customer_name, customer_city, so customer data moves to its own table:

customers   (customer_id PK, customer_name, customer_city)
orders      (order_id PK, order_date, customer_id FK → customers)
products    (product_id PK, product_name, list_price)
order_lines (order_id FK, product_id FK, qty, unit_price, PK (order_id, product_id))

Each fact now lives once: a city change is one UPDATE, products exist without orders, and deleting an order keeps the customer.

BCNF: every determinant is a candidate key. 3NF tables can still break this when there are overlapping candidate keys. Example: each customer has one advisor per product type, and each advisor handles exactly one product type:

The key is (customer_id, product_type), but advisor_id → product_type and advisor_id is not a key, so A7's product type is repeated and could become inconsistent. BCNF decomposition: advisors(advisor_id PK, product_type) and customer_advisors(customer_id, advisor_id). The trade-off: the rule "one advisor per product type per customer" can no longer be enforced by a single table key (BCNF does not always preserve dependencies).

13. Denormalization

Denormalization deliberately stores redundant or pre-joined data to make reads faster and simpler. It is not "bad design": it is the right choice for analytical workloads that read far more than they write, and where pipelines (not users) keep the copies consistent.

Example: building a denormalized sales table from the 3NF model

CREATE TABLE sales_wide AS
SELECT ol.order_id,
       o.order_date,
       c.customer_id, c.customer_name, c.customer_city,      -- copied from customers
       p.product_id,  p.product_name,                       -- copied from products
       ol.qty, ol.unit_price,
       ol.qty * ol.unit_price AS line_amount                 -- derived column
FROM order_lines ol
JOIN orders    o ON o.order_id    = ol.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products  p ON p.product_id  = ol.product_id;

-- dashboards now query one table with no joins
SELECT customer_city, SUM(line_amount) AS revenue
FROM sales_wide
GROUP BY customer_city;

• Costs: more storage (cheap, and columnar formats compress repeated values well), slower or more complex updates, and the risk of copies drifting apart if pipelines are wrong.
• Handle change explicitly: rebuild aggregates after loads, MERGE changed dimension attributes, and decide per attribute whether history matters (SCD Type 1 overwrite vs Type 2 history).
• Where it fits: normalized sources and silver entities; denormalized gold marts, serving tables and feature tables.

14. Star & Snowflake Design Exercises

Interviewers usually give a business scenario and ask you to design the model, then write queries against it. Always answer in this order: business process → grain → dimensions → facts (measures), then mention SCDs and special cases.

Exercise 1: Design a star schema for a retail chain

• Grain: one row per product per sales receipt line.
• Dimensions: date, product (with subcategory/category flattened in), store (with city/region), promotion.
• Measures: quantity, sales_amount, discount_amount, cost_amount (all additive). Profit is derived as sales − cost.
• Degenerate dimension: receipt_number stays on the fact (useful for basket analysis) with no dimension table.

          dim_date ─────┐                 ┌───── dim_product
  (date_key, date,      │                 │      (product_key, sku, product_name,
   month, quarter, year)│                 │       subcategory, category, brand)
                        ▼                 ▼
                    ┌──────── fact_sales ────────┐
                    │ date_key, product_key,      │
                    │ store_key, promo_key,       │
                    │ receipt_number (degenerate),│
                    │ qty, sales_amount,          │
                    │ discount_amount, cost_amount│
                    └─────────────────────────────┘
                        ▲                 ▲
          dim_store ────┘                 └───── dim_promotion
  (store_key, store_name,                        (promo_key, promo_name,
   city, region)                                  promo_type, start_date, end_date)

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,          -- 20261026
  full_date  DATE,
  month      INT,
  month_name VARCHAR(10),
  quarter    INT,
  year       INT
);
CREATE TABLE dim_product (
  product_key  INT PRIMARY KEY,         -- surrogate key
  sku          VARCHAR(20),             -- natural/business key
  product_name VARCHAR(100),
  subcategory  VARCHAR(50),
  category     VARCHAR(50)
);
CREATE TABLE dim_store (
  store_key  INT PRIMARY KEY,
  store_name VARCHAR(100),
  city       VARCHAR(50),
  region     VARCHAR(50)
);
CREATE TABLE dim_promotion (
  promo_key  INT PRIMARY KEY,
  promo_name VARCHAR(100),
  promo_type VARCHAR(30)                 -- includes a "No promotion" row (key 0)
);
CREATE TABLE fact_sales (
  date_key        INT REFERENCES dim_date(date_key),
  product_key     INT REFERENCES dim_product(product_key),
  store_key       INT REFERENCES dim_store(store_key),
  promo_key       INT REFERENCES dim_promotion(promo_key),
  receipt_number  VARCHAR(30),
  qty             INT,
  sales_amount    DECIMAL(12,2),
  discount_amount DECIMAL(12,2),
  cost_amount     DECIMAL(12,2)
);

Use a "No promotion" row (key 0) instead of NULL foreign keys, so inner joins never drop sales and reports show an explicit label.

SELECT d.year, d.month, p.category, s.region,
       SUM(f.sales_amount)                 AS revenue,
       SUM(f.sales_amount - f.cost_amount) AS profit
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
JOIN dim_store   s ON s.store_key   = f.store_key
WHERE d.year = 2026
GROUP BY d.year, d.month, p.category, s.region
ORDER BY d.month, revenue DESC;

SELECT store_name, product_name, revenue
FROM (
  SELECT s.store_name, p.product_name, SUM(f.sales_amount) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY s.store_key ORDER BY SUM(f.sales_amount) DESC) AS rn
  FROM fact_sales f
  JOIN dim_store   s ON s.store_key   = f.store_key
  JOIN dim_product p ON p.product_key = f.product_key
  GROUP BY s.store_key, s.store_name, p.product_name
) ranked
WHERE rn <= 2
ORDER BY store_name, revenue DESC;

SELECT d.year, d.quarter,
       SUM(CASE WHEN pr.promo_key <> 0 THEN f.sales_amount ELSE 0 END) AS promo_revenue,
       SUM(CASE WHEN pr.promo_key =  0 THEN f.sales_amount ELSE 0 END) AS regular_revenue
FROM fact_sales f
JOIN dim_date      d  ON d.date_key   = f.date_key
JOIN dim_promotion pr ON pr.promo_key = f.promo_key
GROUP BY d.year, d.quarter
ORDER BY d.year, d.quarter;

Exercise 2: Snowflake vs star for the same question

CREATE TABLE sf_category    (category_id INT PRIMARY KEY, category_name VARCHAR(50));
CREATE TABLE sf_subcategory (subcategory_id INT PRIMARY KEY, subcategory_name VARCHAR(50),
                             category_id INT REFERENCES sf_category(category_id));
CREATE TABLE sf_product     (product_key INT PRIMARY KEY, product_name VARCHAR(100),
                             subcategory_id INT REFERENCES sf_subcategory(subcategory_id));

-- snowflake: three hops from the fact to reach the category
SELECT c.category_name, SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN sf_product     p  ON p.product_key     = f.product_key
JOIN sf_subcategory sc ON sc.subcategory_id = p.subcategory_id
JOIN sf_category    c  ON c.category_id     = sc.category_id
GROUP BY c.category_name;

-- flatten the hierarchy into one star dimension (rebuilt by the pipeline)
CREATE TABLE dim_product_flat AS
SELECT p.product_key, p.product_name,
       sc.subcategory_name AS subcategory,
       c.category_name     AS category
FROM sf_product p
JOIN sf_subcategory sc ON sc.subcategory_id = p.subcategory_id
JOIN sf_category    c  ON c.category_id     = sc.category_id;

-- star: one hop
SELECT p.category, SUM(f.sales_amount) AS revenue
FROM fact_sales f
JOIN dim_product_flat p ON p.product_key = f.product_key
GROUP BY p.category;

Exercise 3: A banking transactions mart

CREATE TABLE dim_customer (
  customer_key   INT PRIMARY KEY,       -- one row per version (SCD2)
  customer_id    VARCHAR(20),
  segment        VARCHAR(20),
  effective_from DATE, effective_to DATE, is_current INT
);
CREATE TABLE dim_account (account_key INT PRIMARY KEY, account_no VARCHAR(20), product VARCHAR(20));
CREATE TABLE bridge_account_customer (
  account_key    INT REFERENCES dim_account(account_key),
  customer_key   INT REFERENCES dim_customer(customer_key),
  allocation_pct DECIMAL(5,2)              -- shares per account sum to 1.00
);
CREATE TABLE dim_txn_flags (
  flags_key INT PRIMARY KEY, is_international INT, is_card_present INT, is_reversed INT
);
CREATE TABLE fact_card_txn (
  txn_id         VARCHAR(30),               -- degenerate dimension
  txn_date_key   INT,                       -- role-playing dim_date #1
  post_date_key  INT,                       -- role-playing dim_date #2
  account_key    INT REFERENCES dim_account(account_key),
  flags_key      INT REFERENCES dim_txn_flags(flags_key),
  channel        VARCHAR(10),
  amount         DECIMAL(12,2)
);

SELECT c.customer_id,
       SUM(f.amount * b.allocation_pct) AS allocated_spend
FROM fact_card_txn f
JOIN dim_txn_flags fl ON fl.flags_key = f.flags_key
JOIN bridge_account_customer b ON b.account_key = f.account_key
JOIN dim_customer c ON c.customer_key = b.customer_key
WHERE fl.is_reversed = 0
GROUP BY c.customer_id
ORDER BY c.customer_id;

Without the allocation, a ₹1,000 transaction on a joint account would count ₹1,000 for each owner and the grand total would be overstated. This "double counting through a bridge" is exactly what interviewers check.

SELECT f.txn_id, td.full_date AS txn_date, pd.full_date AS posting_date
FROM fact_card_txn f
JOIN dim_date td ON td.date_key = f.txn_date_key     -- same table,
JOIN dim_date pd ON pd.date_key = f.post_date_key    -- two roles
WHERE f.txn_date_key <> f.post_date_key;

Exercise 4: Which kind of fact table?

CREATE TABLE fact_promo_coverage (date_key INT, product_key INT, store_key INT, promo_key INT);

SELECT pc.date_key, pc.store_key, pc.product_key
FROM fact_promo_coverage pc
LEFT JOIN fact_sales f
       ON f.date_key = pc.date_key AND f.product_key = pc.product_key
      AND f.store_key = pc.store_key AND f.promo_key = pc.promo_key
WHERE f.product_key IS NULL;

Exercise 5: Year-over-year growth on a star schema

WITH yearly AS (
  SELECT d.year, p.category, SUM(f.sales_amount) AS revenue
  FROM fact_sales f
  JOIN dim_date d    ON d.date_key    = f.date_key
  JOIN dim_product p ON p.product_key = f.product_key
  GROUP BY d.year, p.category
)
SELECT year, category, revenue,
       LAG(revenue) OVER (PARTITION BY category ORDER BY year) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (PARTITION BY category ORDER BY year))
             / LAG(revenue) OVER (PARTITION BY category ORDER BY year), 1) AS growth_pct
FROM yearly
ORDER BY category, year;

15. Modelling Interview Questions

16. High-Cardinality Columns

Cardinality is the number of distinct values in a column. High: close to the row count (txn_id, account_id, email). Low: a few repeating values (channel, status, currency). Measure with COUNT(DISTINCT col) or approx_count_distinct(col).

Rule: partition only by low-to-medium cardinality columns that queries filter on (usually a date, aiming for ≥ ~1 GB per partition); use clustering for high-cardinality filter columns. Rapidly changing high-cardinality attributes in SCD2 dimensions belong in banded mini-dimensions.

17. SCD Type 2 with Spark

Partitioning is not required for SCD2. First prepare the source: one row per business key, plus a hash of the tracked columns.

TRACKED = ["name", "city", "segment"]
latest = Window.partitionBy("customer_id").orderBy(F.col("updated_at").desc())

src = (raw.withColumn("_rn", F.row_number().over(latest))
          .filter("_rn = 1").drop("_rn")
          # NULL-safe: concat_ws skips NULLs, so a change to NULL would be missed
          .withColumn("hash_diff", F.sha2(F.to_json(F.struct(*TRACKED)), 256))
          .withColumn("effective_from", F.to_date(F.lit(load_date))))

Method A: Delta, one MERGE with a staged union

Changed rows appear twice in the source: once with their key (to close the current row) and once with a NULL key (which never matches, so it is inserted as the new version).

MERGE INTO dim_customer AS t
USING (
  SELECT s.customer_id AS merge_key, s.* FROM stg_customer s
  UNION ALL
  SELECT NULL AS merge_key, s.*
  FROM stg_customer s
  JOIN dim_customer d
    ON d.customer_id = s.customer_id AND d.is_current AND d.hash_diff <> s.hash_diff
) AS s
ON t.customer_id = s.merge_key AND t.is_current = true
WHEN MATCHED AND t.hash_diff <> s.hash_diff THEN
  UPDATE SET t.is_current = false, t.effective_to = date_sub(s.effective_from, 1)
WHEN NOT MATCHED THEN
  INSERT (customer_sk, customer_id, name, city, segment, hash_diff,
          effective_from, effective_to, is_current)
  VALUES (xxhash64(s.customer_id, s.effective_from), s.customer_id, s.name, s.city,
          s.segment, s.hash_diff, s.effective_from, DATE'9999-12-31', true);

The MERGE is atomic and idempotent: rerunning the same batch changes nothing. Even without partitions, Delta rewrites only the files that contain matched rows.

Method B: Parquet / Hive, rebuild and swap

Build the new dimension as history ∪ unchanged current rows ∪ changed rows closed ∪ new versions and new keys (a full outer join of current vs source). Spark cannot overwrite a path it is reading, so write to a new location and swap with ALTER TABLE … SET LOCATION.

• Surrogate keys: an identity column on Databricks, a deterministic hash, or max(sk) + row_number().
• Decide how deletes are handled (keep current, or close with a deleted flag) and process extracts in order.
• On Databricks, AUTO CDC … STORED AS SCD TYPE 2 in declarative pipelines does this declaratively.